package com.easyidea.its.domain.repository.surveillance.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;

import com.easyidea.its.domain.model.surveillance.VehicleInfo;
import com.easyidea.its.domain.repository.surveillance.VehicleInfoRepository;
import com.easyidea.its.util.Config;

public class VehicleInfoRepositoryImpl implements VehicleInfoRepository {
	
	String MY_PROJECTS_SQL = "SELECT XH, HPZL, HPHM, CLPP1, CLXH,  CLPP2, GCJK, ZZG, ZZCMC, "
			+ " CLSBDH, FDJH, CLLX, CSYS, SYXZ, SFZMHM, SFZMMC, SYR, SYQ, CCDJRQ, DJRQ, YXQZ, QZBFQZ,"
			+ " FZJG, GLBM, FPRQ, FZRQ, FDJRQ, FHGZRQ, BXZZRQ, BPCS, BZCS, BDJCS, DJZSBH, ZDJZSHS, DABH, "
			+ " XZQH, ZT, DYBJ, JBR, CLLY, LSH, FDJXH, RLZL, PL, GL, ZXXS, CWKC, CWKK, CWKG, HXNBCD, HXNBKD, "
			+ " HXNBGD, GBTHPS, ZS, ZJ, QLJ, HLJ, LTGG, LTS, ZZL, ZBZL, HDZZL, HDZK, ZQYZL, QPZK, HPZK, "
			+ "HBDBQK, CCRQ, HDFS, LLPZ1, PZBH1, LLPZ2, PZBH2, XSDW, XSJG, XSRQ, JKPZ, JKPZHM, HGZBH, NSZM, "
			+ "NSZMBH, GXRQ, XGZL, QMBH, HMBH, BZ, JYW, ZSXZQH, ZSXXDZ, YZBM1, LXDH, ZZZ, ZZXZQH, ZZXXDZ,"
			+ " YZBM2, ZDYZT, YXH, CYRY, DPHGZBH, SQDM, CLYT, YTSX, DZYX, XSZBH, SJHM, JYHGBZBH ";

	private JdbcTemplate jdbcTemplate;
	private LobHandler lobHandler;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@SuppressWarnings("unchecked")
	public List<VehicleInfo> getVehicleInfo(String plateNumber) {
		
		if (plateNumber == null) {
			return null;
		}
		String FROM = " FROM VEHICLEINFO ";
		String fzjg = plateNumber.substring(0, 2);
		plateNumber = plateNumber.substring(1);
		String sql = "";
		
		if(Config.getString("show.vehicle.image").equals("true")) {
			sql = MY_PROJECTS_SQL + ","+ Config.getString("vehicle.image.field").toUpperCase() + FROM +  " where HPHM = ? and FZJG = ?";	
		} else {
			sql = MY_PROJECTS_SQL + FROM +  " where HPHM = ? and FZJG = ?";
		}
		return jdbcTemplate.query(sql, new Object[] { plateNumber, fzjg },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						VehicleInfo info = new VehicleInfo();
						info.setXH(rs.getString("XH"));
						info.setHPZL(rs.getString("HPZL"));
						info.setHPHM(rs.getString("HPHM"));
						info.setCLPP1(rs.getString("CLPP1"));
						info.setCLXH(rs.getString("CLXH"));
						info.setCLPP2(rs.getString("CLPP2"));
						info.setGCJK(rs.getString("GCJK"));
						info.setZZG(rs.getString("ZZG"));
						info.setZZCMC(rs.getString("ZZCMC"));
						info.setCLSBDH(rs.getString("CLSBDH"));
						info.setFDJH(rs.getString("FDJH"));
						info.setCLLX(rs.getString("CLLX"));
						info.setCSYS(rs.getString("CSYS"));
						info.setSYXZ(rs.getString("SYXZ"));
						info.setSFZMHM(rs.getString("SFZMHM"));
						info.setSFZMMC(rs.getString("SFZMMC"));
						info.setSYR(rs.getString("SYR"));
						info.setZSXZQH(rs.getString("ZSXZQH"));
						info.setZSXXDZ(rs.getString("ZSXXDZ"));
						info.setYZBM1(rs.getString("YZBM1"));
						info.setLXDH(rs.getString("LXDH"));
						info.setZZZ(rs.getString("ZZZ"));
						info.setZZXZQH(rs.getString("ZZXZQH"));
						info.setZZXXDZ(rs.getString("ZZXXDZ"));
						info.setYZBM2(rs.getString("YZBM2"));
						info.setSYQ(rs.getString("SYQ"));
						info.setCCDJRQ(rs.getString("CCDJRQ"));
						info.setDJRQ(rs.getString("DJRQ"));
						info.setYXQZ(rs.getString("YXQZ"));
						info.setQZBFQZ(rs.getString("QZBFQZ"));
						info.setFZJG(rs.getString("FZJG"));
						info.setGLBM(rs.getString("GLBM"));
						info.setFPRQ(rs.getString("FPRQ"));
						info.setFZRQ(rs.getString("FZRQ"));
						info.setFDJRQ(rs.getString("FDJRQ"));
						info.setFHGZRQ(rs.getString("FHGZRQ"));
						info.setBXZZRQ(rs.getString("BXZZRQ"));
						info.setBPCS(rs.getString("BPCS"));
						info.setBZCS(rs.getString("BZCS"));
						info.setBDJCS(rs.getString("BDJCS"));
						info.setDJZSBH(rs.getString("DJZSBH"));
						info.setZDJZSHS(rs.getString("ZDJZSHS"));
						info.setDABH(rs.getString("DABH"));
						info.setXZQH(rs.getString("XZQH"));
						info.setZT(rs.getString("ZT"));
						info.setZDYZT(rs.getString("ZDYZT"));
						info.setDYBJ(rs.getString("DYBJ"));
						info.setJBR(rs.getString("JBR"));
						info.setCLLY(rs.getString("CLLY"));
						info.setLSH(rs.getString("LSH"));
						info.setFDJXH(rs.getString("FDJXH"));
						info.setRLZL(rs.getString("RLZL"));
						info.setPL(rs.getString("PL"));
						info.setGL(rs.getString("GL"));
						info.setZXXS(rs.getString("ZXXS"));
						info.setCWKC(rs.getString("CWKC"));
						info.setCWKK(rs.getString("CWKK"));
						info.setCWKG(rs.getString("CWKG"));
						info.setHXNBCD(rs.getString("HXNBCD"));
						info.setHXNBKD(rs.getString("HXNBKD"));
						info.setHXNBGD(rs.getString("HXNBGD"));
						info.setGBTHPS(rs.getString("GBTHPS"));
						info.setZS(rs.getString("ZS"));
						info.setZJ(rs.getString("ZJ"));
						info.setQLJ(rs.getString("QLJ"));
						info.setHLJ(rs.getString("HLJ"));
						info.setLTGG(rs.getString("LTGG"));
						info.setLTS(rs.getString("LTS"));
						info.setZZL(rs.getString("ZZL"));
						info.setZBZL(rs.getString("ZBZL"));
						info.setHDZZL(rs.getString("HDZZL"));
						info.setHDZK(rs.getString("HDZK"));
						info.setZQYZL(rs.getString("ZQYZL"));
						info.setQPZK(rs.getString("QPZK"));
						info.setHPZK(rs.getString("HPZK"));
						info.setHBDBQK(rs.getString("HBDBQK"));
						info.setCCRQ(rs.getString("CCRQ"));
						info.setHDFS(rs.getString("HDFS"));
						info.setLLPZ1(rs.getString("LLPZ1"));
						info.setPZBH1(rs.getString("PZBH1"));
						info.setLLPZ2(rs.getString("LLPZ2"));
						info.setPZBH2(rs.getString("PZBH2"));
						info.setXSDW(rs.getString("XSDW"));
						info.setXSJG(rs.getString("XSJG"));
						info.setXSRQ(rs.getString("XSRQ"));
						info.setJKPZ(rs.getString("JKPZ"));
						info.setJKPZHM(rs.getString("JKPZHM"));
						info.setHGZBH(rs.getString("HGZBH"));
						info.setNSZM(rs.getString("NSZM"));
						info.setNSZMBH(rs.getString("NSZMBH"));
						info.setGXRQ(rs.getString("GXRQ"));
						info.setXGZL(rs.getString("XGZL"));
						info.setQMBH(rs.getString("QMBH"));
						info.setHMBH(rs.getString("HMBH"));
						info.setBZ(rs.getString("BZ"));
						info.setJYW(rs.getString("JYW"));
						info.setYXH(rs.getString("YXH"));
						if(Config.getString("show.vehicle.image").equals("true")) {
							byte[] memo = lobHandler.getBlobAsBytes(rs, Config.getString("vehicle.image.field").toUpperCase()); 
							info.setMemo(memo);
						}
						return info;
					}
				});
	}

	public void getLob() {
		jdbcTemplate.query("select memo from SELECT where XH=?",
				new Object[] { 2L }, new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return lobHandler.getBlobAsBytes(rs, 1);
					}
				});
	}

	public void updateMemo(final String xh) {
		String sql = " update VEHICLEINFO set memo = ? where  XH = ?";
		jdbcTemplate.execute(sql,
				new AbstractLobCreatingPreparedStatementCallback(
						this.lobHandler) {
					protected void setValues(PreparedStatement ps,
							LobCreator lobCreator) throws SQLException {
						// 存储文件流，text文本之类
						// 存储图片用lobCreator.setBlobAsBinaryStream(ps, 3,
						// inputstream,inputstream.avaliable)
						File file = new File("D:/log/test.jpg");
						InputStream inputstream = null;
						try {
							inputstream = new FileInputStream(file);
						} catch (FileNotFoundException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} // 以byte流的方式打开文件
						int i = 0;
						try {
							i = inputstream.available();
						} catch (IOException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						} // 得到文件大小
//						byte[] data = new byte[i];
//						inputstream.read(data); // 读数据
//						inputstream.close();
						lobCreator.setBlobAsBinaryStream(ps, 1, inputstream,
								i);
						ps.setString(2, xh);
					}
				});

	}

	public void setLobHandler(LobHandler lobHandler) {
		this.lobHandler = lobHandler;
	}
}
